package util;

import java.io.IOException;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;


public class DBHelp<T> {

	
	private static BasicDataSource  basic = new BasicDataSource();
	
	static{
		
		Properties pr = new Properties();
		
		try {
			pr.load(DBHelp.class.getClassLoader().getResourceAsStream("db.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		String driver = pr.getProperty("driver");
		String url = pr.getProperty("url");
		String username = pr.getProperty("username");
		String password = pr.getProperty("password");
		
		basic.setDriverClassName(driver);
		basic.setUrl(url);
		basic.setUsername(username);
		basic.setPassword(password);
		basic.setInitialSize(5);
		basic.setMaxActive(20);
		basic.setMaxWait(5000);
		basic.setMinIdle(10);
		
	}
	
	private Connection getConnection() throws SQLException{
		
		Connection conn = basic.getConnection();
		return conn;
	}
	
	public int executeSQL(String sql,Object...args){
		Connection conn=null;
		PreparedStatement ps=null;
		int raws=0;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);
				
			}
			raws=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(null,ps,conn);
		}
		return raws;
		
	}
	public T queryForObject(String sql,RowMapper<T> mapper,Object...args){
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T obj = null;
		
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			
			if(rs.next()) {
				obj = mapper.mapperRow(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat, conn);
		}
		return obj;
	}

	public List<T> queryForList(String sql,RowMapper<T> mapper,Object...args){
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<T> list  = new ArrayList<T>();
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			
			while(rs.next()) {
				T obj = mapper.mapperRow(rs);
				list.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat, conn);
		}
		return list;
		
		
	}
	private void close(ResultSet rs,PreparedStatement ps, Connection conn) {
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(ps!=null){
					try {
						ps.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}finally{
						if(conn!=null){
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
					}
				}
			}
		}
		
	}



	
}
